package com.xuxueli.applyModules.utils.exporttables;
import com.lowagie.text.Font;
import com.lowagie.text.*;
import com.lowagie.text.rtf.RtfWriter2;
import org.apache.commons.lang3.StringUtils;

import java.awt.*;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * <p> 创建word文档 </p>
 * @description : 步骤:
 *  1、建立文档
 *  2、创建一个书写器
 *  3、打开文档
 *  4、向文档中写入数据
 *  5、关闭文档
 * select table_name as name,table_comment as comment from information_schema.tables where table_schema =#{dbName} order by table_name"
 *成功
 */
public class TestAllTableWordSuccess {

    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";
    private static final String SCHEMA = "gaia";
    private static final String PATH = "D://data";

    /**
     * 生成word文档
     *
     * @param tables：该数据库下所有表信息
     * @param fileName：生成文件地址
     * @param title:文件内容标题
     * @return: void
     */
    public static void toWord(List<Tables> tables, String fileName, String title, String path) {
        Document document = new Document(PageSize.A4);
        try {
            // 创建文件夹
            File dir = new File(path);
            if(!dir.exists()){
                dir.mkdirs();
            }
            // 创建文件
            File file = new File(fileName);
            if (file.exists() && file.isFile()) {
                file.delete();
            }
            file.createNewFile();

            // 写入文件信息
            RtfWriter2.getInstance(document, new FileOutputStream(fileName));
            document.open();
            Paragraph ph = new Paragraph();
            Font f = new Font();
            Paragraph p = new Paragraph(title, new Font(Font.NORMAL, 24, Font.BOLDITALIC, new Color(0, 0, 0)));
            p.setAlignment(1);
            document.add(p);
            ph.setFont(f);
            for (int i = 0; i < tables.size(); i++) {
                String table_name = tables.get(i).getName();
                String table_comment = tables.get(i).getComment();
                String sql = "SELECT ordinal_position,column_name,column_type, column_key, extra ,is_nullable, column_default, column_comment,data_type,character_maximum_length "
                        + "FROM information_schema.columns WHERE table_schema='"+SCHEMA+"' and table_name='"+table_name+"'";
                ResultSet rs = SqlUtils.getResultSet(SqlUtils.getConnnection(USERNAME, PASSWORD),sql);
                String sqls = "show create table "+table_name;
                String resultSets = SqlUtils.getResultSets(SqlUtils.getConnnection(USERNAME, PASSWORD), sqls);
                List<TableFileds> fileds = new ArrayList<>();
                while(rs.next()){
                    TableFileds tableFileds = new TableFileds();
                    tableFileds.setField(rs.getString("column_name"));
                    String column_comment = rs.getString("column_comment");
                    if(StringUtils.isBlank(column_comment) || "null".equals(column_comment)){
                        tableFileds.setComment("字段描述");
                    }else{
                        tableFileds.setComment(column_comment);
                    }
                    tableFileds.setType(rs.getString("data_type"));
//                    String character_maximum_length = rs.getString("character_maximum_length");
//                    if(StringUtils.isBlank(character_maximum_length) || "null".equals(character_maximum_length)){
//                        map.put("长度","");
//                    }else{
//                        map.put("长度",character_maximum_length);
//                    }
                    tableFileds.setNull(rs.getString("is_nullable"));
                    String column_default = rs.getString("column_default");
                    if(StringUtils.isBlank(column_default) || "null".equals(column_default)){
                        tableFileds.setDefault("默认值");
                    }else{
                        tableFileds.setDefault(column_default);
                    }
                    String column_key = rs.getString("column_key");
                    if(StringUtils.isBlank(column_key) || "null".equals(column_key)){
                        tableFileds.setKey("");
                    }else{
                        tableFileds.setKey(column_default);
                    }
                    fileds.add(tableFileds);

                }
                String all = "" + (i + 1) + " 表名称:" + table_name + "（" + table_comment + "）";
                Table table = new Table(6);
                document.add(new Paragraph(""));
                table.setBorderWidth(1);
                table.setPadding(0);
                table.setSpacing(0);
                //添加表头的元素，并设置表头背景的颜色
                Color chade = new Color(176, 196, 222);
                Cell cell = new Cell("编号");
                addCell(table, cell, chade);
                cell = new Cell("字段名");
                addCell(table, cell, chade);
                cell = new Cell("类型");
                addCell(table, cell, chade);
                cell = new Cell("是否非空");
                addCell(table, cell, chade);
                cell = new Cell("是否主键");
                addCell(table, cell, chade);
                cell = new Cell("注释");
                addCell(table, cell, chade);
                table.endHeaders();
                // 表格的主体
                for (int k = 0; k < fileds.size(); k++) {
                    addContent(table, cell, (k + 1) + "");
                    addContent(table, cell, fileds.get(k).getField());
                    addContent(table, cell, fileds.get(k).getType());
                    addContent(table, cell, fileds.get(k).getNull().equals("YES") ? "否" : "是");
                    addContent(table, cell, fileds.get(k).getKey() != "" ? "是" : "否");
                    addContent(table, cell, fileds.get(k).getComment());
                }
                Paragraph pheae = new Paragraph(all);
                //写入表说明
                document.add(pheae);
                //生成表格
                document.add(table);
                String alls = " 建表语句:\n"+"\t"+resultSets;
                Paragraph pheaes = new Paragraph(alls);
                //建表语句
                document.add(pheaes);
            }
            document.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 添加表头到表格
     *
     * @param table
     * @param cell
     * @param chade
     */
    private static void addCell(Table table, Cell cell, Color chade) {
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(chade);
        table.addCell(cell);
    }

    /**
     * 添加内容到表格
     *
     * @param table
     * @param content
     */
    private static void addContent(Table table, Cell cell, String content) {
        cell = new Cell(content);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        table.addCell(cell);
    }

    public static void main(String[] args) throws SQLException {
        String sqltable = "SELECT table_name, table_type , ENGINE,table_collation,table_comment, create_options FROM information_schema.TABLES WHERE table_schema='"+SCHEMA+"'";
        ResultSet rss = SqlUtils.getResultSet(SqlUtils.getConnnections(USERNAME, PASSWORD),sqltable);
        List<Tables> tablesList = new ArrayList<>();
        while(rss.next()){
            Tables tables = new Tables();
            String table_comment = rss.getString("table_comment");
            if(StringUtils.isBlank(table_comment) || "null".equals(table_comment)){
                tables.setComment("未知表描述");
            }else{
                tables.setComment(table_comment);
            }
            String tableName = rss.getString("table_name");
            tables.setName(tableName);
            tablesList.add(tables);
        }

        toWord(tablesList,PATH + File.separator + "database.doc","数据库建表",PATH );
    }

}